package com.bidepan.spring6.jdbc;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.junit.jupiter.SpringJUnitConfig;

import java.util.List;

@SpringJUnitConfig(locations="classpath:beans.xml")
public class JdbcTemplateTest {
    @Autowired
    private JdbcTemplate jdbcTemplate;

//    查询
//    返回单个对象
    @Test
    public void testSelectObject(){
        String sql = "SELECT * FROM t_emp where id=?";
        Emp emp = jdbcTemplate.queryForObject(sql,
                new BeanPropertyRowMapper<>(Emp.class),1);
        System.out.println(emp.toString());
    }
//    返回list集合
    @Test
    public void testSelectList(){
        String sql = "SELECT * FROM t_emp";
        List<Emp> empList = jdbcTemplate.query(sql
                , new BeanPropertyRowMapper<>(Emp.class));
        for(Emp emp:empList){
            System.out.println(emp.toString());
        }
    }

//    返回单个值
    @Test
    public void testSelectOneValue(){
        String sql = "SELECT COUNT(*) FROM t_emp";
        Long count = jdbcTemplate.queryForObject(sql, Long.class);
        System.out.println("表中有"+count+"条数据");
    }

    //------------------------------------------------------------------------------------------------------------------------

    //添加，修改，删除
    //        第一步：编写sql语句
    //        第二步调用JdbcTemplate的update(sql,object[]..args)
    @Test
    public void testUpdate(){
//        1.添加数据
        String sql = "INSERT INTO t_emp VALUES (null,?,?,?)";
        Object[] parmas = {"林冲",40,"男"};
        int rows = jdbcTemplate.update(sql,parmas);
        System.out.println(rows);

//        2.修改数据
        String sql2 = "update t_emp set name=? where name=?";
        int rows2 = jdbcTemplate.update(sql2,"林冲","林教头");
        System.out.println(rows2);

//        3.删除数据
        String sql3 = "DELETE FROM t_emp WHERE name=?";
        int rows3 = jdbcTemplate.update(sql3,"林冲");
        System.out.println(rows3);
    }
}
